package com.bank.dao;

import com.bank.entity.Customer;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class CustomerDAO {
    private static final Logger LOGGER = Logger.getLogger(CustomerDAO.class.getName());
    private static final boolean USE_VIEW = true; // 是否使用视图查询

    /**
     * 注册客户基本信息
     */
    public static boolean registerCustomerBasic(String name, String pid, String phone, String address) throws SQLException {
        String sql = "INSERT INTO userInfo (customerName, PID, telephone, address) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             // 关键参数：Statement.RETURN_GENERATED_KEYS,数据库实现ID自增
             PreparedStatement ps = conn.prepareStatement(sql)) {
            // 设置参数值
            ps.setString(1, name);
            ps.setString(2, pid);
            ps.setString(3, phone);
            ps.setString(4, address);
            // 执行SQL
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "注册客户失败", e);
            throw e;
        }
    }

    /**
     * 根据姓名和身份证号获取客户ID
     */
    public static int getCustomerId(String name, String pid) throws SQLException {
        String sql = "SELECT customerID FROM userInfo WHERE customerName = ? AND PID = ?";
        try (Connection conn = DBUtil.getConnection();

             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, pid);
            // 获取自动生成的主键（客户ID）
            try (ResultSet rs = ps.executeQuery()) {
                // 将数据库生成的ID设置回Customer对象（ID回填机制）
                return rs.next() ? rs.getInt("customerID") : -1;// 将数据库生成的ID设置到对象中
            }
        }
    }

    /**
     * 获取所有客户列表
     */
    public List<Customer> getAllCustomers() throws SQLException {
        List<Customer> list = new ArrayList<>();
        String sql = USE_VIEW ? "SELECT * FROM v_客户信息总览" : "SELECT * FROM userInfo";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                list.add(mapResultSetToCustomer(rs));
            }
        }
        return list;
    }

    /**
     * 客户登录验证
     */
    public static boolean loginByCustomerNameAndPassword(String customerName, String password) throws SQLException {
        String sql = "SELECT * FROM userInfo WHERE customerName = ? AND password = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, customerName);
            ps.setString(2, password);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "客户登录失败", e);
            throw e;
        }
    }





    //修改客户信息
    public void updateCustomer(Customer customer) throws SQLException {
        String sql = "UPDATE userInfo SET customerName=?, PID=?, telephone=?, address=? WHERE customerID=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, customer.getCustomerName());
            ps.setString(2, customer.getPID());
            ps.setString(3, customer.getTelephone());
            ps.setString(4, customer.getAddress());
            ps.setInt(5, customer.getCustomerID());
            ps.executeUpdate();
        }
    }
    // 添加根据ID查询客户的方法
    public Customer getCustomerByID(int customerID) throws SQLException {
        String sql = USE_VIEW ?
                "SELECT * FROM v_客户信息总览 WHERE 客户编号 = ?" :
                "SELECT * FROM userInfo WHERE customerID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, customerID);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? mapResultSetToCustomer(rs) : null;
            }
        }
    }

    // 修复添加客户方法
    public void addCustomer(Customer customer) throws SQLException {
        String sql = "INSERT INTO userInfo (customerName, PID, telephone, address) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, customer.getCustomerName());
            ps.setString(2, customer.getPID());
            ps.setString(3, customer.getTelephone());
            ps.setString(4, customer.getAddress());
            ps.executeUpdate();

            // 获取生成的ID
            try (ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) {
                    customer.setCustomerID(rs.getInt(1));
                }
            }
        }
    }
    /**
     * 删除客户
     */
    public void deleteCustomer(int customerID) throws SQLException {
        String sql = "DELETE FROM userInfo WHERE customerID=?";//根据客户ID删除记录
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, customerID);//将Java变量绑定到SQL参数
            ps.executeUpdate();//返回受影响的行数
        }
    }


    /**
     * 精确查询：根据身份证号查询客户
     *///根据 USE_VIEW 标志自动切换中英文列名映射，统一的映射逻辑
    //查询方法统一接口，所有查询方法自动适配视图/原始表
    public Customer getCustomerByPID(String PID) throws SQLException {
        String sql = USE_VIEW ?
                "SELECT * FROM v_客户信息总览 WHERE 身份证号 = ?" :
                "SELECT * FROM userInfo WHERE PID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, PID);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? mapResultSetToCustomer(rs) : null;
            }
        }
    }

    /**
     * 模糊查询：根据姓名模糊查询客户列表
     */
    public List<Customer> getCustomersByNameLike(String namePattern) throws SQLException {
        List<Customer> list = new ArrayList<>();
        String sql = USE_VIEW ?
                "SELECT * FROM v_客户信息总览 WHERE 姓名 LIKE ?" :
                "SELECT * FROM userInfo WHERE customerName LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "%" + namePattern + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToCustomer(rs));
                }
            }
        }
        return list;
    }

    /**
     * 范围查询：根据客户编号范围查询客户列表
     */
    public List<Customer> getCustomersByIDRange(int startID, int endID) throws SQLException {
        List<Customer> list = new ArrayList<>();
        String sql = USE_VIEW ?
                "SELECT * FROM v_客户信息总览 WHERE 客户编号 BETWEEN ? AND ?" :
                "SELECT * FROM userInfo WHERE customerID BETWEEN ? AND ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, startID);
            ps.setInt(2, endID);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToCustomer(rs));
                }
            }
        }
        return list;
    }

    /**
     * 统一结果集封装方法
     */
    private Customer mapResultSetToCustomer(ResultSet rs) throws SQLException {
        Customer customer = new Customer();

        if (USE_VIEW) {
            // 视图列名（中文）
            customer.setCustomerID(rs.getInt("客户编号"));
            customer.setCustomerName(rs.getString("姓名"));
            customer.setPID(rs.getString("身份证号"));
            customer.setTelephone(rs.getString("电话"));
            customer.setAddress(rs.getString("地址"));
        } else {
            // 原始表列名（英文）
            customer.setCustomerID(rs.getInt("customerID"));
            customer.setCustomerName(rs.getString("customerName"));
            customer.setPID(rs.getString("PID"));
            customer.setTelephone(rs.getString("telephone"));
            customer.setAddress(rs.getString("address"));
        }

        return customer;
    }
}